﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class OrderDal : BaseDataAccess
    {
        /// <summary>
        /// 通过订单号 获取订单信息
        /// </summary>
        /// <param name="sOrderNo"></param>
        /// <returns></returns>
        public OrderModel GetModelByOrderNo(string sOrderNo)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBOrder WHERE OrderNo=@OrderNo ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@OrderNo", DbType.String, sOrderNo);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<OrderModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }


        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public OrderModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBOrder");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<OrderModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {  
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(@"SELECT 
                            (select HotelsName from TBHotels where ID=HotelsID) HotelsName,
                            (select RoomName from TBRoom where ID=RoomID) RoomName,
                            (select Name from TBDictionaryType where Type=7 and Code= OrderStatus) OrderStatusName,
                            * FROM TBOrder  ORDER BY OrderStatus,UpdateTime DESC");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(OrderModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBOrder
                (
                        OrderNo
                        ,UserID
                        ,HotelsID
                        ,RoomID
                        ,CheckDate
                        ,DepartureDate
                        ,RoomQuantity
                        ,LatestTime
                        ,ContactName
                        ,ContactPhone
                        ,ContactEmail
                        ,OtherClaims
                        ,OtherClaimsRemark
                        ,ReservationNumber
                        ,CopePrice
                        ,DeductiblePrice
                        ,NightNumberPrice
                        ,PreferentialPrice
                        ,ActuallyPaidPrice
                        ,OrderStatus
                        ,PayMethod
                        ,RefundTime
                        ,PayTime
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                        ,RoomFacilitiesCode
                )
                VALUES
                (
                        @OrderNo
                       ,@UserID
                       ,@HotelsID
                       ,@RoomID
                       ,@CheckDate
                       ,@DepartureDate
                       ,@RoomQuantity
                       ,@LatestTime
                       ,@ContactName
                       ,@ContactPhone
                       ,@ContactEmail
                       ,@OtherClaims
                       ,@OtherClaimsRemark
                       ,@ReservationNumber
                       ,@CopePrice
                       ,@DeductiblePrice
                       ,@NightNumberPrice
                       ,@PreferentialPrice
                       ,@ActuallyPaidPrice
                       ,@OrderStatus
                       ,@PayMethod
                       ,@RefundTime
                       ,@PayTime
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
,@RoomFacilitiesCode
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@OrderNo", DbType.String, model.OrderNo);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
            SetParameter(ldcDBCommand, "@CheckDate", DbType.DateTime, model.CheckDate);
            SetParameter(ldcDBCommand, "@DepartureDate", DbType.DateTime, model.DepartureDate);
            SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
            SetParameter(ldcDBCommand, "@LatestTime", DbType.DateTime, model.LatestTime);
            SetParameter(ldcDBCommand, "@ContactName", DbType.String, model.ContactName);
            SetParameter(ldcDBCommand, "@ContactPhone", DbType.String, model.ContactPhone);
            SetParameter(ldcDBCommand, "@ContactEmail", DbType.String, model.ContactEmail);
            SetParameter(ldcDBCommand, "@OtherClaims", DbType.String, model.OtherClaims);
            SetParameter(ldcDBCommand, "@OtherClaimsRemark", DbType.String, model.OtherClaimsRemark);
            SetParameter(ldcDBCommand, "@ReservationNumber", DbType.String, model.ReservationNumber);
            SetParameter(ldcDBCommand, "@CopePrice", DbType.Decimal, model.CopePrice);
            SetParameter(ldcDBCommand, "@DeductiblePrice", DbType.Decimal, model.DeductiblePrice);
            SetParameter(ldcDBCommand, "@NightNumberPrice", DbType.Decimal, model.NightNumberPrice);
            SetParameter(ldcDBCommand, "@PreferentialPrice", DbType.Decimal, model.PreferentialPrice);
            SetParameter(ldcDBCommand, "@ActuallyPaidPrice", DbType.Decimal, model.ActuallyPaidPrice);
            SetParameter(ldcDBCommand, "@OrderStatus", DbType.Int32, model.OrderStatus);
            SetParameter(ldcDBCommand, "@PayMethod", DbType.Int32, model.PayMethod);
            SetParameter(ldcDBCommand, "@RefundTime", DbType.DateTime, model.RefundTime);
            SetParameter(ldcDBCommand, "@PayTime", DbType.DateTime, model.PayTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime); 
            SetParameter(ldcDBCommand, "@RoomFacilitiesCode", DbType.String, model.RoomFacilitiesCode);
                    
                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(OrderModel model)
        {
            int iTmp = -1;
            try
            {
				
            string sqlString = string.Format(@"
                UPDATE TBOrder
                SET
                    OrderNo = @OrderNo
                    ,UserID = @UserID
                    ,HotelsID = @HotelsID
                    ,RoomID = @RoomID
                    ,CheckDate = @CheckDate
                    ,DepartureDate = @DepartureDate
                    ,RoomQuantity = @RoomQuantity
                    ,LatestTime = @LatestTime
                    ,ContactName = @ContactName
                    ,ContactPhone = @ContactPhone
                    ,ContactEmail = @ContactEmail
                    ,OtherClaims = @OtherClaims
                    ,OtherClaimsRemark = @OtherClaimsRemark
                    ,ReservationNumber = @ReservationNumber
                    ,CopePrice = @CopePrice
                    ,DeductiblePrice = @DeductiblePrice
                    ,NightNumberPrice = @NightNumberPrice
                    ,PreferentialPrice = @PreferentialPrice
                    ,ActuallyPaidPrice = @ActuallyPaidPrice
                    ,OrderStatus = @OrderStatus
                    ,PayMethod = @PayMethod
                    ,RefundTime = @RefundTime
                    ,PayTime = @PayTime
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,RoomFacilitiesCode=@RoomFacilitiesCode
                WHERE
                    ID = @ID
            ");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);
            //绑定SQL参数
            SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
            SetParameter(ldcDBCommand, "@OrderNo", DbType.String, model.OrderNo);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
            SetParameter(ldcDBCommand, "@CheckDate", DbType.DateTime, model.CheckDate);
            SetParameter(ldcDBCommand, "@DepartureDate", DbType.DateTime, model.DepartureDate);
            SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
            SetParameter(ldcDBCommand, "@LatestTime", DbType.DateTime, model.LatestTime);
            SetParameter(ldcDBCommand, "@ContactName", DbType.String, model.ContactName);
            SetParameter(ldcDBCommand, "@ContactPhone", DbType.String, model.ContactPhone);
            SetParameter(ldcDBCommand, "@ContactEmail", DbType.String, model.ContactEmail);
            SetParameter(ldcDBCommand, "@OtherClaims", DbType.String, model.OtherClaims);
            SetParameter(ldcDBCommand, "@OtherClaimsRemark", DbType.String, model.OtherClaimsRemark);
            SetParameter(ldcDBCommand, "@ReservationNumber", DbType.String, model.ReservationNumber);
            SetParameter(ldcDBCommand, "@CopePrice", DbType.String, model.CopePrice);
            SetParameter(ldcDBCommand, "@DeductiblePrice", DbType.String, model.DeductiblePrice);
            SetParameter(ldcDBCommand, "@NightNumberPrice", DbType.String, model.NightNumberPrice);
            SetParameter(ldcDBCommand, "@PreferentialPrice", DbType.String, model.PreferentialPrice);
            SetParameter(ldcDBCommand, "@ActuallyPaidPrice", DbType.String, model.ActuallyPaidPrice);
            SetParameter(ldcDBCommand, "@OrderStatus", DbType.Int32, model.OrderStatus);
            SetParameter(ldcDBCommand, "@PayMethod", DbType.Int32, model.PayMethod);
            SetParameter(ldcDBCommand, "@RefundTime", DbType.DateTime, model.RefundTime);
            SetParameter(ldcDBCommand, "@PayTime", DbType.DateTime, model.PayTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
            SetParameter(ldcDBCommand, "@RoomFacilitiesCode", DbType.String, model.RoomFacilitiesCode);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateByModel(OrderModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBOrder
                SET
                    OrderNo = @OrderNo
                    ,UserID = @UserID
                    ,HotelsID = @HotelsID
                    ,RoomID = @RoomID
                    ,CheckDate = @CheckDate
                    ,DepartureDate = @DepartureDate
                    ,RoomQuantity = @RoomQuantity
                    ,LatestTime = @LatestTime
                    ,ContactName = @ContactName
                    ,ContactPhone = @ContactPhone
                    ,ContactEmail = @ContactEmail
                    ,OtherClaims = @OtherClaims
                    ,OtherClaimsRemark = @OtherClaimsRemark
                    ,ReservationNumber = @ReservationNumber
                    ,CopePrice = @CopePrice
                    ,DeductiblePrice = @DeductiblePrice
                    ,NightNumberPrice = @NightNumberPrice
                    ,PreferentialPrice = @PreferentialPrice
                    ,ActuallyPaidPrice = @ActuallyPaidPrice
                    ,OrderStatus = @OrderStatus
                    ,PayMethod = @PayMethod
                WHERE
                    ID = @ID
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
                SetParameter(ldcDBCommand, "@OrderNo", DbType.String, model.OrderNo);
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
                SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
                SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
                SetParameter(ldcDBCommand, "@CheckDate", DbType.DateTime, model.CheckDate);
                SetParameter(ldcDBCommand, "@DepartureDate", DbType.DateTime, model.DepartureDate);
                SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
                SetParameter(ldcDBCommand, "@LatestTime", DbType.DateTime, model.LatestTime);
                SetParameter(ldcDBCommand, "@ContactName", DbType.String, model.ContactName);
                SetParameter(ldcDBCommand, "@ContactPhone", DbType.String, model.ContactPhone);
                SetParameter(ldcDBCommand, "@ContactEmail", DbType.String, model.ContactEmail);
                SetParameter(ldcDBCommand, "@OtherClaims", DbType.String, model.OtherClaims);
                SetParameter(ldcDBCommand, "@OtherClaimsRemark", DbType.String, model.OtherClaimsRemark);
                SetParameter(ldcDBCommand, "@ReservationNumber", DbType.String, model.ReservationNumber);
                SetParameter(ldcDBCommand, "@CopePrice", DbType.String, model.CopePrice);
                SetParameter(ldcDBCommand, "@DeductiblePrice", DbType.String, model.DeductiblePrice);
                SetParameter(ldcDBCommand, "@NightNumberPrice", DbType.String, model.NightNumberPrice);
                SetParameter(ldcDBCommand, "@PreferentialPrice", DbType.String, model.PreferentialPrice);
                SetParameter(ldcDBCommand, "@ActuallyPaidPrice", DbType.String, model.ActuallyPaidPrice);
                SetParameter(ldcDBCommand, "@OrderStatus", DbType.Int32, model.OrderStatus);
                SetParameter(ldcDBCommand, "@PayMethod", DbType.Int32, model.PayMethod);

                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }


        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBOrder");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBOrder");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 查询订单页面上的基本信息
        /// </summary>
        /// <param name="hotelid"></param>
        /// <param name="roomid"></param>
        /// <param name="bt"></param>
        /// <param name="et"></param>
        /// <returns></returns>
        public DataSet GetHotelRoomInfo(int usertype, int hotelid, int roomid, string bt, string et)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_GetHotelRoomInfo");
                SetParameter(ldcDBCommand, "@USER_TYPE", DbType.Int32, usertype);
                SetParameter(ldcDBCommand, "@HOTEL_ID", DbType.Int32, hotelid);
                SetParameter(ldcDBCommand, "@ROOM_ID", DbType.Int32, roomid);
                SetParameter(ldcDBCommand, "@BEGIN_TIME", DbType.String, bt);
                SetParameter(ldcDBCommand, "@END_TIME", DbType.String, et);
                DataSet ds = base.GetDataSet(ldcDBCommand);
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加订单，还未付款
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddNoPayOrder(OrderInfo_Order_Model model, int userid)
        {
            int iTmp = -1;
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_AddNoPayOrder");
                SetParameter(ldcDBCommand, "@OrderNo", DbType.String, DateTime.Now.ToString("yyyyMMddhhmmssfff"));
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, userid);
                SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelID);
                SetParameter(ldcDBCommand, "@RoomID", DbType.String, model.RoomID);
                SetParameter(ldcDBCommand, "@CheckDate", DbType.DateTime, Convert.ToDateTime(model.BeginTime));
                SetParameter(ldcDBCommand, "@DepartureDate", DbType.DateTime, Convert.ToDateTime(model.EndTime));
                SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, Convert.ToInt32(model.RoomQuantity));
                SetParameter(ldcDBCommand, "@LatestTime", DbType.DateTime, Convert.ToDateTime(model.Arrive));
                SetParameter(ldcDBCommand, "@ContactName", DbType.String, model.Customer);
                SetParameter(ldcDBCommand, "@ContactPhone", DbType.String, model.Phone);
                SetParameter(ldcDBCommand, "@ContactEmail", DbType.String, model.Email);
                SetParameter(ldcDBCommand, "@RoomFacilitiesCode", DbType.String, model.RequireID);
                SetParameter(ldcDBCommand, "@OtherClaimsRemark", DbType.String, model.Remark);
                SetParameter(ldcDBCommand, "@DaySpan", DbType.Int32, model.DaySpan);
                SetParameter(ldcDBCommand, "@CopePrice", DbType.Decimal, Convert.ToDecimal(model.Price));
                SetParameter(ldcDBCommand, "@UserName1", DbType.String, model.UserName1);
                SetParameter(ldcDBCommand, "@UserName2", DbType.String, model.UserName2);
                SetParameter(ldcDBCommand, "@TotalPrice", DbType.Int32, model.TotalPrice);
                SetParameter(ldcDBCommand, "@Result", DbType.Int32, iTmp, ParameterDirection.Output);
                base.ExecuteNonQuery(ldcDBCommand);
                iTmp = Convert.ToInt32(ldcDBCommand.Parameters["@Result"].Value.ToString());
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 添加订单，还未付款
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public OrderPay_Order_Model GetNoPayOrder(int orderid, int userid)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_GetNoPayOrder");
                SetParameter(ldcDBCommand, "@OrderID", DbType.Int32, orderid);
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, userid);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<OrderPay_Order_Model>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 根据订单号，更新订单表
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public int UpdateOrder(string orderNo, int consumeIntegration, decimal deductiblePrice, decimal nightNumberPrice, decimal actuallyPaidPrice, int payMethod, int userID)
        {
            int iTmp = -1;
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_UpdateOrder");
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, userID);
                SetParameter(ldcDBCommand, "@OrderNo", DbType.String, orderNo);
                SetParameter(ldcDBCommand, "@ConsumeIntegration", DbType.Int32, consumeIntegration);
                SetParameter(ldcDBCommand, "@DeductiblePrice", DbType.Decimal, deductiblePrice);
                SetParameter(ldcDBCommand, "@NightNumberPrice", DbType.Decimal, nightNumberPrice);
                SetParameter(ldcDBCommand, "@ActuallyPaidPrice", DbType.Decimal, actuallyPaidPrice);
                SetParameter(ldcDBCommand, "@PayMethod", DbType.Int32, payMethod);
                SetParameter(ldcDBCommand, "@Result", DbType.Int32, iTmp, ParameterDirection.Output);
                base.ExecuteNonQuery(ldcDBCommand);
                iTmp = Convert.ToInt32(ldcDBCommand.Parameters["@Result"].Value.ToString());
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }
    }
}

